﻿Imports System.Data
Imports System.Data.SqlClient

Public Class historicoCargasDAL

    Sub New()

    End Sub

    Function inserir(ByVal carga As Integer, ByVal dt As Date, ByVal descricao As String) As Integer
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim codigo As Integer
        Try
            cmd.CommandText = "INSERT INTO historico_carga (car_codigo, hc_data, hc_descricao) VALUES (@carga, @data, @descricao);"
            cmd.Parameters.Add(New SqlParameter("@carga", SqlDbType.Int)).Value = carga
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.Date)).Value = dt
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.Text)).Value = descricao
            codigo = objD.executacomandoAI(cmd, "historico_carga")
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return codigo
    End Function

    Sub alterar(ByVal codigo As Integer, ByVal carga As Integer, ByVal dt As Date, ByVal descricao As String)
        Dim cmd As New SqlCommand
        Dim objD As New Dados

        Try
            cmd.CommandText = "UPDATE historico_carga set car_codigo = @carga, hc_data = @data, hc_descricao = @descricao WHERE hc_codigo = @codigo;"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            cmd.Parameters.Add(New SqlParameter("@carga", SqlDbType.Int)).Value = carga
            cmd.Parameters.Add(New SqlParameter("@data", SqlDbType.Date)).Value = dt
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

    Sub excluir(ByVal codigo As Integer)
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Try
            cmd.CommandText = "DELETE FROM historico_carga WHERE hc_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub

  


    Function localizarCarga(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            cmd.CommandText = "SELECT * FROM historico_carga h LEFT JOIN cargas c ON c.car_protocolo like @descricao"
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao + "%"
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT hc_codigo = max(hc_codigo) FROM historico_carga "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT hc_codigo = min(hc_codigo) FROM historico_carga "
            Else
                cmd.CommandText = "SELECT * FROM vw_historico_carga WHERE car_protocolo like @descricao"
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descricao + "%"
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)

        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizar(ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            cmd.CommandText = "SELECT * FROM vw_historico_carga WHERE hc_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT hc_codigo FROM historico_carga WHERE hc_codigo = (SELECT MIN(hc_codigo) FROM historico_carga WHERE hc_codigo > @codigo)"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT hc_codigo FROM historico_carga  WHERE hc_codigo = (SELECT MAX(hc_codigo) FROM historico_carga WHERE hc_codigo < @codigo)"
            End If
            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            'Execução do comando
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        tab = Nothing
    End Function
End Class
